﻿/*----------------------------------------------------------------
 * 版权所有 (c) 2025 HaiTangYunchi  保留所有权利
 * CLR版本：4.0.30319.42000
 * 公司名称：
 * 命名空间：SalaryCalculator.lib
 * 唯一标识：612010ef-61d1-44e8-aa9b-899800fccbce
 * 文件名：DatabaseHelper
 * 
 * 创建者：海棠云螭
 * 电子邮箱：haitangyunchi@126.com
 * 创建时间：2025/5/10 20:11:36
 * 版本：V1.0.0
 * 描述：
 *
 * ----------------------------------------------------------------
 * 修改人：
 * 时间：
 * 修改说明：
 *
 * 版本：V1.0.1
 *----------------------------------------------------------------*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.IO;

namespace SalaryCalculator
{
    /// <summary>
    /// 数据库帮助类，负责数据库初始化和连接管理
    /// </summary>
    public static class DatabaseHelper
    {
        private const string ConnectionString = "Data Source=Database.db;Version=3;";

        /// <summary>
        /// 初始化数据库，创建表和索引
        /// </summary>
        public static void InitializeDatabase()
        {
            if (!File.Exists("Database.db"))
            {
                SQLiteConnection.CreateFile("Database.db");

                using (var connection = new SQLiteConnection(ConnectionString))
                {
                    connection.Open();

                    // 创建员工表
                    string createEmployeesTable = @"
                    CREATE TABLE Employees (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        Name TEXT NOT NULL,
                        HourlyRate REAL NOT NULL,
                        IsActive INTEGER DEFAULT 1
                    );";

                    // 创建工作记录表
                    string createWorkRecordsTable = @"
                    CREATE TABLE WorkRecords (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        EmployeeId INTEGER NOT NULL,
                        WorkDate TEXT NOT NULL,
                        StartTime TEXT NOT NULL,
                        EndTime TEXT NOT NULL,
                        BreakMinutes INTEGER NOT NULL,
                        IsSettled INTEGER DEFAULT 0,
                        FOREIGN KEY(EmployeeId) REFERENCES Employees(Id)
                    );";

                    // 创建结算记录表
                    string createSettlementRecordsTable = @"
                    CREATE TABLE SettlementRecords (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        EmployeeId INTEGER NOT NULL,
                        StartDate TEXT NOT NULL,
                        EndDate TEXT NOT NULL,
                        SettlementDate TEXT NOT NULL,
                        TotalHours REAL NOT NULL,
                        TotalAmount REAL NOT NULL,
                        FOREIGN KEY(EmployeeId) REFERENCES Employees(Id)
                    );";

                    ExecuteNonQuery(connection, createEmployeesTable);
                    ExecuteNonQuery(connection, createWorkRecordsTable);
                    ExecuteNonQuery(connection, createSettlementRecordsTable);
                }
            }
        }

        /// <summary>
        /// 获取数据库连接
        /// </summary>
        public static SQLiteConnection GetConnection()
        {
            return new SQLiteConnection(ConnectionString);
        }

        private static void ExecuteNonQuery(SQLiteConnection connection, string sql)
        {
            using (var command = new SQLiteCommand(sql, connection))
            {
                command.ExecuteNonQuery();
            }
        }
    }
}